/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package interact;

import GUI.ShowMessageDialog;
import entity.Employee;
import java.awt.Component;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.EventObject;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultCellEditor;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableCellEditor;

/**
 *
 * @author UnderGround
 */
public class DataInteraction {

    public static ResultSetMetaData rsmd;
    private static Employee emp;
    private static CallableStatement call;
    private static ResultSet rs;
    private static java.sql.Statement st;
    private static DefaultTableModel dtm;


    public static DefaultTableModel Employee_checkIn(JTable table, int EmpID){
         String id = "" ;
         ResultSet rs1 = null;
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            try {
                call = connect.conn.prepareCall("{call getIDBorrow(?)}");
                call.setInt(1, EmpID);
                rs = call.executeQuery();
                while(rs.next()){
                     id +=rs.getString("BookID")+"|";
                }
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println(id);
              try {
                call = connect.conn.prepareCall("{call getBook_checkIn(?)}");
                call.setString(1, id);
                rs1 = call.executeQuery();
                
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }

            rsmd = rs1.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs1.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs1.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return dtm;
    }



     public static DefaultTableModel SearchEmployee(JTable table, String keyword) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            try {
                call = connect.conn.prepareCall("{call searchEmployee(?)}");
                call.setString(1, keyword);
                rs = call.executeQuery();
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }

            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return dtm;
    }


    public static boolean check_Borrower(int bookID) {
        int status = 0;
        try {
            call = connect.conn.prepareCall("{call check_Borrower(?)}");
            call.setInt(1, bookID);
            rs = call.executeQuery();
            while (rs.next()) {
                status = rs.getInt("Quantity_Borrower");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (status == 0) {
            return false;
        } else {
            return true;
        }

    }

    public static int Check_status(int empID, int bookID) {
        int status = 0;
        try {
            call = connect.conn.prepareCall("{call check_status(?,?)}");
            call.setInt(1, empID);
            call.setInt(2, bookID);
            rs = call.executeQuery();
            while (rs.next()) {
                status = rs.getInt("check_status");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return status;
    }

    public static int Check_status(Vector condition) {
        int check = 0;
        String sqlStatement = "{call check_status(";
        for (int i = 0; i < condition.size(); i++) {
            Object obj = condition.get(i);
            if (obj instanceof Integer) {
                sqlStatement = sqlStatement + Integer.parseInt(obj.toString());
            } else {
                sqlStatement = sqlStatement + "'" + obj.toString() + "'";
            }
            if (i < condition.size() - 1) {
                sqlStatement = sqlStatement + ",";
            }
        }
        sqlStatement = sqlStatement + ")}";
        CallableStatement stmt;
        try {
            stmt = connect.conn.prepareCall(sqlStatement);
            rs = stmt.executeQuery();
            while (rs.next()) {
                check = rs.getInt("check_status");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return check;
    }

    public static String getItemCombobox(String id, String procName, String FieldName) {
        String item = null;
        try {
            call = connect.conn.prepareCall("{call " + procName + "()}");
            call.setString(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                item = rs.getString(FieldName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return item;
    }

    public static int getIDCombobox(String item, String procName, String FieldName) {
        int id = 0;
        try {
            call = connect.conn.prepareCall("{call " + procName + "(?)}");
            call.setString(1, item);
            rs = call.executeQuery();
            while (rs.next()) {
                id = Integer.parseInt(rs.getString(FieldName));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }

    public static void loadDataToCombobox(JComboBox cbx, String ProcName, String FieldName) {
        try { System.out.println(FieldName);
            call = connect.conn.prepareCall("{call "+ProcName+"()}");
            rs = call.executeQuery();
            System.out.println(rs);
            cbx.addItem("--------Select item--------");
            while (rs.next()) {
                System.out.println(rs.getString(FieldName));
                cbx.addItem(rs.getString(FieldName));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static ResultSet viewEmployeeInfo() {
        try {
            call = connect.conn.prepareCall("{call ViewEmployeeInfo()}");
            rs = call.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public static String getISBN(String item) {
        String isbn = null;
        try {
            call = connect.conn.prepareCall("{call getISBN(?)}");
            call.setString(1, item);
            rs = call.executeQuery();
            while (rs.next()) {
                isbn = rs.getString("ISBN");
            }

        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return isbn;
    }

    public static String getSequence() {
        String sequence = null;
        try {
            call = connect.conn.prepareCall("{call getSequence()}");
            rs = call.executeQuery();
            while (rs.next()) {
                sequence = rs.getString("Sequence");
            }

        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sequence;
    }

    public static void deleteRecord(String procName, String id) {
        new ShowMessageDialog(null, true, "Are you sure ?", "Validation Message", ShowMessageDialog.CONFIRM);
        if (ShowMessageDialog.STATUS == ShowMessageDialog.OK) {
            try {
                call = connect.conn.prepareCall("{call " + procName + "(?)}");
                call.setString(1, id);

                call.executeUpdate();
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            return;
        }


    }

    public static ResultSet SeachBook(String model, String keyword) {
        try {
            call = connect.conn.prepareCall("{call seachBook(?,?)}");
            call.setString(1, keyword);
            call.setString(2, model);
            rs = call.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public static ResultSet setAdminInfo() {
        try {
            call = connect.conn.prepareCall("{call ViewAdminInfo()}");
            rs = call.executeQuery();

        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public static void updateData(String procName, Vector v) {
        int check = 0;
        String sqlStatement = "{call " + procName + " (";
        for (int i = 0; i < v.size(); i++) {
            Object obj = v.get(i);
            if (obj instanceof Integer) {
                sqlStatement = sqlStatement + Integer.parseInt(obj.toString());
            } else {
                sqlStatement = sqlStatement + "'" + obj.toString() + "'";
            }
            if (i < v.size() - 1) {
                sqlStatement = sqlStatement + ",";
            }
        }
        sqlStatement = sqlStatement + ")}";
        CallableStatement stmt;
        try {
            stmt = connect.conn.prepareCall(sqlStatement);
            check = stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static DefaultTableModel loadDataTableWhenSearch(JTable table, int start, int conditon, String keyword) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            try {
                call = connect.conn.prepareCall("{call Search(?,?,?)}");
                call.setInt(1, start);
                call.setInt(2, conditon);
                call.setString(3, keyword);
                rs = call.executeQuery();
            } catch (SQLException ex) {
                Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }

            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            vtCols.add("Select");
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }

            table.getColumnModel().getColumn(6).setCellEditor(new CheckBoxCellEditor());
            table.getColumnModel().getColumn(6).setCellRenderer(new CheckBoxCellRender());

            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return dtm;
    }

    public static int countBooksRecord() {
        int num = 0;
        try {
            call = connect.conn.prepareCall("{call countBooks()}");
            rs = call.executeQuery();
            while (rs.next()) {
                num = Integer.parseInt(rs.getString("numberRecord"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return num;

    }

    public static DefaultTableModel getBook_UserChooceToTable(String callNumber, JTable table) {
        try {
            dtm = (DefaultTableModel) table.getModel();
            for (int i = dtm.getRowCount() - 1; i >= 0; i--) {
                dtm.removeRow(i);
            }
            Vector vtCols = new Vector();
            call = connect.conn.prepareCall("{call getBook_UserChooce(?)}");
            call.setString(1, callNumber);
            rs = call.executeQuery();

            rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                vtCols.add(rsmd.getColumnName(i));
            }
            dtm.setColumnIdentifiers(vtCols);
            while (rs.next()) {
                Vector vtRow = new Vector();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    vtRow.add(rs.getString(i));
                }
                dtm.addRow(vtRow);
            }
            table.setModel(dtm);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return dtm;
    }

    public static int getLastID() {
        int id = 0;
        try {
            st = connect.conn.createStatement();
            rs = st.executeQuery("SELECT lastID = SCOPE_IDENTITY()");
            while (rs.next()) {
                id = rs.getInt("lastID");
            }
        } catch (SQLException ex) {
            Logger.getLogger(connect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }

    public static String getField(String procName, String FieldName, String condition) {
        String value = null;
        try {
            call = connect.conn.prepareCall("{call " + procName + "(?)}");
            call.setString(1, condition);
            rs = call.executeQuery();
            while (rs.next()) {
                value = rs.getString(FieldName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return value;
    }

    public static void setTextArea(JTextArea text, int bookID) {
        try {
            call = connect.conn.prepareCall("{call ViewBookDetails(?)}");
            call.setInt(1, bookID);
            rs = call.executeQuery();
            while (rs.next()) {
                text.setText("Call Number :" + rs.getString("CallNumber") + "\n"
                        + "ISBN :" + rs.getString("ISBN") + "\n"
                        + "Title :" + rs.getString("AuthorName"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

     public static ResultSet getReport() {
        try {
             call = connect.conn.prepareCall("{call Report()}");
            rs = call.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(connect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rs;
    }
    public static void main(String agrs[]) {
        connect.configServer();
        //System.out.println(getLastID());
        String a;
//        System.out.println(getISBN("category"));
        System.out.println(getSequence());

    }
}

class CheckBoxCellRender extends DefaultTableCellRenderer {

    @Override
    public Component getTableCellRendererComponent(JTable table, Object value,
            boolean isSelected, boolean hasFocus, int row, int column) {
        JCheckBox ch = new JCheckBox();
        boolean bVal = false;
        if (value != null) {
            bVal = Boolean.parseBoolean(value.toString());
        }
        ch.setSelected(bVal);
        int qty = Integer.parseInt(table.getModel().getValueAt(row, 5).toString());
        if(qty==0){
            System.out.println("Khong");
            return this;
        }
        return ch;
    }
}

class CheckBoxCellEditor extends DefaultCellEditor implements TableCellEditor {

    private int row;
    public CheckBoxCellEditor() {
        super(new JCheckBox());
    }
   
    public Component getTableCellEditorComponent(JTable table, Object value,
						boolean isSelected,
						boolean expanded,
						boolean leaf, int row) {

        JCheckBox ch = new JCheckBox();
        ch.setSelected(Boolean.parseBoolean(value.toString()));
        int qty = Integer.parseInt(table.getModel().getValueAt(row, 5).toString());
        if(qty==0){
            System.out.println("Khong");
            ch.setEnabled(false);
        }
        return ch;
    }
}
